package org.qpcr.community.device.service;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hswebframework.ezorm.core.param.Sort;
import org.hswebframework.ezorm.core.param.Term;
import org.hswebframework.reactor.excel.CellDataType;
import org.hswebframework.reactor.excel.ExcelHeader;
import org.hswebframework.web.api.crud.entity.PagerResult;
import org.hswebframework.web.api.crud.entity.QueryParamEntity;
import org.hswebframework.web.crud.service.GenericReactiveCrudService;
import org.hswebframework.web.exception.BusinessException;
import org.qpcr.community.device.entity.CalibrationExperimentalRecordsEntity;
import org.qpcr.community.device.entity.DeviceProperty;
import org.qpcr.community.device.service.data.DeviceDataService;
import org.qpcr.community.device.web.request.GetFluorescenceCalibrationRequest;
import org.qpcr.community.device.web.response.GetFluorescenceCalibrationResponse;
import org.qpcr.community.elastic.search.service.ElasticSearchService;
import org.springframework.core.io.buffer.DataBufferUtils;
import org.springframework.http.codec.multipart.FilePart;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestBody;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Slf4j
@RequiredArgsConstructor
public class CalibrationExperimentalRecordsService extends GenericReactiveCrudService<CalibrationExperimentalRecordsEntity, String> {

    private final ElasticSearchService elasticSearchService;
    private final DeviceDataService deviceDataService;

    /**
     * 解析Excel文件并转换为DataAnalysisExcelDataResponse对象
     * 用于反解析ExperimentalRecordsController.exportExcel方法导出的Excel文件
     *
     * @param filePart Excel文件
     * @return DataAnalysisExcelDataResponse对象
     */
    public Mono<Integer> parseExcelToExperimentalRecords(FilePart filePart, String deviceId, String moduleCode,String projectName,Long receiveTime) {
        return filePart.content()
                       .collectList()
                       .flatMap(dataBuffers -> {
                           if (dataBuffers.isEmpty()) {
                               return Mono.error(new IllegalArgumentException("上传的文件为空"));
                           }

                           byte[] bytes = dataBuffers.stream()
                                                     .map(dataBuffer -> {
                                                         byte[] array = new byte[dataBuffer.readableByteCount()];
                                                         dataBuffer.read(array);
                                                         DataBufferUtils.release(dataBuffer);
                                                         return array;
                                                     })
                                                     .reduce(new byte[0], (a, b) -> {
                                                         byte[] result = new byte[a.length + b.length];
                                                         System.arraycopy(a, 0, result, 0, a.length);
                                                         System.arraycopy(b, 0, result, a.length, b.length);
                                                         return result;
                                                     });

                           if (bytes.length == 0) {
                               return Mono.error(new IllegalArgumentException("上传的文件大小为0"));
                           }

                           try (InputStream is = new ByteArrayInputStream(bytes);
                                Workbook workbook = new XSSFWorkbook(is)) {

                               // 校验Excel文件中的sheet页
//                               validateExcelSheets(workbook);

                               CalibrationExperimentalRecordsEntity entity = new CalibrationExperimentalRecordsEntity();

                               // 设置基本信息
                               entity.setDataSource("excel");
                               entity.setReceiveTime(receiveTime);
                               entity.setCreateTime(receiveTime);
                               entity.setModifyTime(receiveTime);
                               entity.setName(projectName);
                               entity.setState("100"); // 设置为100表示成功
                               entity.setType(0); // 正常实验记录
                               entity.setResult(1); // 设置实验结果

                               // 解析实验信息表（Info表）
                               Sheet infoSheet = workbook.getSheet("实验信息");
                               if (infoSheet != null) {
                                   parseInfoSheet(infoSheet, entity);
                               }

                               // 解析设备状态信息表
                               Sheet deviceStateSheet = workbook.getSheet("状态信息");
                               if (deviceStateSheet != null) {
                                   parseDeviceStateSheet(deviceStateSheet, entity);
                               }

                               // 解析温度数据表
                               Sheet temperatureSheet = workbook.getSheet("温度");
                               if (temperatureSheet != null) {
                                   // 校验温度表头
                                   validateTemperatureHeaders(temperatureSheet);
                                   parseTemperatureSheet(temperatureSheet, entity);
                               }

                               // 解析电压电流数据表
                               Sheet volCurrentSheet = workbook.getSheet("电压电流");
                               if (volCurrentSheet != null) {
                                   // 校验电压电流表头
                                   validateVolCurrentHeaders(volCurrentSheet);
                                   parseVolCurrentSheet(volCurrentSheet, entity);
                               }

//                               // 解析扩增数据表
//                               Sheet amplificationSheet = workbook.getSheet("扩增");
//                               if (amplificationSheet != null) {
//                                   // 校验扩增表头
//                                   validateAmplificationHeaders(amplificationSheet);
//                                   parseAmplificationSheet(amplificationSheet, entity);
//                               }

                               // 解析计量数据表
                               Sheet meteringSheet = workbook.getSheet("荧光（电压）");
                               if (meteringSheet != null) {
                                   // 校验荧光（电压）表头
                                   validateFluorescenceVoltageHeaders(meteringSheet);
                                   parseMeteringSheet(meteringSheet, entity);
                               }

                               // 解析荧光（源数据）表
                               Sheet fluorescenceSourceSheet = workbook.getSheet("荧光（原数据）");
                               if (fluorescenceSourceSheet != null) {
                                   // 校验荧光（源数据）表头
                                   validateFluorescenceSourceHeaders(fluorescenceSourceSheet,entity);
                               }

                               // 解析CT数据表
                               Sheet ctSheet = workbook.getSheet("CT");
                               if (ctSheet != null) {
                                   // 校验CT表头
                                   validateCTHeaders(ctSheet);
                                   parseCTSheet(ctSheet, entity);
                               }

                               // 解析熔解曲线数据表
                               Sheet meltCurveSheet = workbook.getSheet("熔解（原数据）");
                               if (meltCurveSheet != null) {
                                   // 校验熔解（原数据）表头
                                   validateMeltCurveHeaders(meltCurveSheet);
                                   parseMeltCurveSheet(meltCurveSheet, entity);
                               }

                               // 解析Trim数据表
                               Sheet trimSheet = workbook.getSheet("熔解（后处理）");
                               if (trimSheet != null) {
                                   // 校验熔解（后处理）表头
                                   validateMeltPostProcessHeaders(trimSheet);
                                   parseTrimSheet(trimSheet, entity);
                               }

                               // 解析熔解结果表
                               Sheet meltingResultsSheet = workbook.getSheet("熔解结果");
                               if (meltingResultsSheet != null) {
                                   // 校验熔解结果表头
                                   validateMeltingResultsHeaders(meltingResultsSheet);
                                   parseMeltingResults(meltingResultsSheet, entity);
                               }

                               // 设置默认值（如果Excel中没有找到对应数据）
                               setDefaultValuesForExperimentalRecords(entity);

                               entity.setDeviceId(deviceId);
                               entity.setModuleCode(moduleCode);
                               entity.setModuleId(moduleCode);
                               return super.insert(entity);
                           } catch (IOException e) {
                               throw new RuntimeException("Error processing Excel file", e);
                           }
                       });
    }



    public Mono<Integer> clear(String deviceId,String moduleCode) {
        return super.createDelete()
                    .is(CalibrationExperimentalRecordsEntity::getDeviceId, deviceId)
                    .is(CalibrationExperimentalRecordsEntity::getModuleCode, moduleCode)
                    .execute();
    }

    public Mono<GetFluorescenceCalibrationResponse> getFluorescenceCalibrationParam(GetFluorescenceCalibrationRequest req) {
        req.validate();

        GetFluorescenceCalibrationResponse resp = new GetFluorescenceCalibrationResponse(new ArrayList<>());

        // 处理云端数据
        Mono<Void> cloudDataMono = Mono.empty();
        if (CollectionUtil.isNotEmpty(req.getCloudIds())) {
            cloudDataMono = Flux.fromIterable(req.getCloudIds())
                .flatMap(id -> {
                    log.info("开始处理云端数据ID: {}", id);
                    return this.getData(id, req.getDeviceId(), "MeteringDataReport", false)
                        .doOnNext(dataResult -> log.info("获取到云端数据，数据量: {}", dataResult.getData().size()))
                        .flatMap(dataResult -> {
                            Set<Object> processedCounts = new HashSet<>();
                            int dataSize = Math.min(dataResult.getData().size(), 10); // 防止越界

                            GetFluorescenceCalibrationResponse.FluorescenceCalibration data = new GetFluorescenceCalibrationResponse.FluorescenceCalibration();
                            data.setVIC(new ArrayList<>());
                            data.setCy5(new ArrayList<>());
                            data.setFAM(new ArrayList<>());
                            data.setROX(new ArrayList<>());

                            for (int i = 0; i < dataSize; i++) {
                                DeviceProperty deviceProperty = dataResult.getData().get(i);
                                JSONObject jsonObject = JSON.parseObject((String) deviceProperty.getValue());
                                Object countValue = jsonObject.get("count") == null ? "" : jsonObject.getIntValue("count");

                                // 检查count是否已经处理过，如果已处理则跳过
                                if (processedCounts.contains(countValue)) {
                                    continue;
                                }
                                // 将count添加到已处理集合中
                                processedCounts.add(countValue);

                                if (null != jsonObject.get("ch1_original")) {
                                    data.getVIC().add(jsonObject.getDoubleValue("ch1_original"));
                                    log.info("取到VIC数据: {}", jsonObject.getDoubleValue("ch1_original"));
                                }
                                if (null != jsonObject.get("ch2_original")) {
                                    data.getCy5().add(jsonObject.getDoubleValue("ch2_original"));
                                    log.info("取到Cy5数据: {}", jsonObject.getDoubleValue("ch2_original"));
                                }
                                if (null != jsonObject.get("ch3_original")) {
                                    data.getFAM().add(jsonObject.getDoubleValue("ch3_original"));
                                    log.info("取到FAM数据: {}", jsonObject.getDoubleValue("ch3_original"));
                                }
                                if (null != jsonObject.get("ch4_original")) {
                                    data.getROX().add(jsonObject.getDoubleValue("ch4_original"));
                                    log.info("取到ROX数据: {}", jsonObject.getDoubleValue("ch4_original"));
                                }
                            }

                            // 确保每个通道只取前10条数据
                            data.setVIC(data.getVIC().stream().limit(10).collect(Collectors.toList()));
                            data.setCy5(data.getCy5().stream().limit(10).collect(Collectors.toList()));
                            data.setFAM(data.getFAM().stream().limit(10).collect(Collectors.toList()));
                            data.setROX(data.getROX().stream().limit(10).collect(Collectors.toList()));

                            resp.getData().add(data);
                            log.info("云端数据处理完成，VIC: {}, Cy5: {}, FAM: {}, ROX: {}",
                                data.getVIC().size(), data.getCy5().size(), data.getFAM().size(), data.getROX().size());

                            return Mono.empty();
                        });
                })
                .then();
        }

        // 处理本地数据
        Mono<Void> localDataMono = Mono.empty();
        if (CollectionUtil.isNotEmpty(req.getLocalIds())) {
            localDataMono = createQuery()
                .in(CalibrationExperimentalRecordsEntity::getId, req.getLocalIds())
                .fetch()
                .collectList()
                .doOnNext(localList -> log.info("获取到本地数据，数量: {}", localList.size()))
                .doOnNext(localList -> {

                    // 处理本地数据
                    for (CalibrationExperimentalRecordsEntity entity : localList) {
                        String paramStr = entity.getFluorescenceCalibrationParam();
                        if (paramStr != null && !paramStr.trim().isEmpty()) {
                            try {

                                List<GetFluorescenceCalibrationResponse.FluorescenceCalibration> calibrations = JSON.parseArray(paramStr, GetFluorescenceCalibrationResponse.FluorescenceCalibration.class);

                                if (CollectionUtil.isNotEmpty(calibrations)) {
                                    GetFluorescenceCalibrationResponse.FluorescenceCalibration calibration = CollectionUtil.getFirst(calibrations);
                                    GetFluorescenceCalibrationResponse.FluorescenceCalibration fluorescenceCalibration = new GetFluorescenceCalibrationResponse.FluorescenceCalibration();
                                    List<Double> vic = calibration
                                        .getVIC()
                                        .stream()
                                        .limit(10)
                                        .collect(Collectors.toList());
                                    List<Double> cy5 = calibration
                                        .getCy5()
                                        .stream()
                                        .limit(10)
                                        .collect(Collectors.toList());
                                    List<Double> fam = calibration
                                        .getFAM()
                                        .stream()
                                        .limit(10)
                                        .collect(Collectors.toList());
                                    List<Double> rox = calibration
                                        .getROX()
                                        .stream()
                                        .limit(10)
                                        .collect(Collectors.toList());
                                    fluorescenceCalibration.setVIC(vic);
                                    fluorescenceCalibration.setCy5(cy5);
                                    fluorescenceCalibration.setFAM(fam);
                                    fluorescenceCalibration.setROX(rox);
                                    resp.getData().add(fluorescenceCalibration);
                                }
                            } catch (Exception e) {
                                log.warn("解析本地荧光校准参数失败: {}", paramStr, e);
                            }
                        }
                    }

                })
                .then();
        }

        // 等待所有数据处理完成后返回结果
        return Mono.when(cloudDataMono, localDataMono)
            .then(Mono.fromCallable(() -> {
                log.info("所有数据处理完成，返回响应，数据项数量: {}", resp.getData().size());
                if (resp.getData().size() != 10){
                    throw new BusinessException("选择的实验数据荧光数据不完整、请重新选择");
                }
                return resp;
            }));
    }


    /*******************************************************************************************************************/
    /*******************************************************Private方法**************************************************/
    /*******************************************************************************************************************/


    private Mono<PagerResult<DeviceProperty>> getData(String recordId, String deviceId, String prop, boolean calc) {
        QueryParamEntity entity = new QueryParamEntity();
        Term termB = Term.of("batchId", "eq", recordId);
        Term termC = Term.of("calc", "eq", calc);
        Term termP = Term.of("property", "eq", prop);
        Sort sortT = new Sort("timestamp").asc();
        entity.addTerm(termB);
        if (calc) {
            entity.addTerm(termC);
        }
        entity.addTerm(termP);
        entity.setSorts(Collections.singletonList(sortT));
        entity.setPaging(false);
        return deviceDataService.queryPropertyPage(deviceId, entity, prop.split(","));
    }

    /**
     * 解析Info表（实验信息）
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseInfoSheet(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 获取第一行（标题行）
            Row titleRow = sheet.getRow(0);
            if (titleRow != null) {
                String title = getStringValue(titleRow.getCell(0));
                if (title != null) {
                    // 解析标题中的设备信息
                    parseTitleInfo(title, entity);
                }
            }

            // 解析序列信息（从第2行开始）
            parseSequenceInfo(sheet, entity);
        } catch (Exception e) {
            log.warn("解析Info表失败", e);
        }
    }

    /**
     * 解析标题中的设备信息
     *
     * @param title  标题字符串
     * @param entity 实体对象
     */
    private void parseTitleInfo(String title, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 示例标题格式：1.01.06.W.S01.0009(60f1b0014201a613)_1(1)_终端用户0009(EndUser0009)
            // #47va4127000000i70001aa#
            // 检测项:（库通用码1),测光通道:（库VIC,Cy5,FAM,ROX),程序:（库通用码1),
            // 完成度:100%实验时长：11分22秒

            // 按行分割
            String[] lines = title.split("\n");

            if (lines.length > 0) {
                // 解析第一行：设备名称和模块信息
                parseDeviceAndModuleInfo(lines[0], entity);
            }

            if (lines.length > 1) {
                // 解析第二行：载体信息
                String carrier = lines[1].replaceAll("[#]", "");
                entity.setCarrier(carrier);
            }

            if (lines.length > 2) {
                // 解析第三行：检测项、测光通道、程序信息
                parseDetectionInfo(lines[2], entity);
            }

            if (lines.length > 3) {
                // 解析第四行：完成度和实验时长
                parseCompletionInfo(lines[3], entity);
            }

        } catch (Exception e) {
            log.warn("解析标题信息失败", e);
        }
    }

    /**
     * 解析设备和模块信息
     *
     * @param deviceModuleLine 设备和模块信息行
     * @param entity           实体对象
     */
    private void parseDeviceAndModuleInfo(String deviceModuleLine, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 格式：1.01.06.W.S01.0009(60f1b0014201a613)_1(1)_终端用户0009(EndUser0009)
            String[] parts = deviceModuleLine.split("_");

            if (parts.length >= 3) {
                // 解析设备信息
                String deviceInfo = parts[0];
                String deviceName = extractBeforeBracket(deviceInfo);
                String deviceId = extractBracketContent(deviceInfo);
                entity.setDeviceName(deviceName);
                if (deviceId != null) {
                    entity.setDeviceId(deviceId);
                }

                // 解析模块信息
                String moduleInfo = parts[1];
                String moduleCode = extractBeforeBracket(moduleInfo);
                String moduleId = extractBracketContent(moduleInfo);
                entity.setModuleCode(moduleCode);
                if (moduleId != null) {
                    entity.setModuleId(moduleId);
                }

                // 解析实验人员信息
                String userInfo = parts[2];
                String creatorName = extractBeforeBracket(userInfo);
                String creatorUserName = extractBracketContent(userInfo);
                entity.setCreatorName(creatorName);
                if (creatorUserName != null) {
                    entity.setCreatorUserName(creatorUserName);
                }
            }
        } catch (Exception e) {
            log.warn("解析设备和模块信息失败", e);
        }
    }

    /**
     * 解析检测信息
     *
     * @param detectionLine 检测信息行
     * @param entity        实体对象
     */
    private void parseDetectionInfo(String detectionLine, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 格式：检测项:（库通用码1),测光通道:（库VIC,Cy5,FAM,ROX),程序:（库通用码1),

            // 解析检测项
            if (detectionLine.contains("检测项:")) {
                int startIndex = detectionLine.indexOf("检测项:") + 4;
                int endIndex = detectionLine.indexOf("),", startIndex);
                if (endIndex == -1) {
                    endIndex = detectionLine.indexOf(")", startIndex);
                }
                if (startIndex < endIndex) {
                    String itemInfo = detectionLine.substring(startIndex, endIndex);
                    // 去掉中英文括号和"库"字
                    String item = itemInfo.replaceAll("[()（）]", "").replaceAll("库", "");
                    entity.setItem(item);
                }
            }

            // 解析测光通道
            if (detectionLine.contains("测光通道:")) {
                int startIndex = detectionLine.indexOf("测光通道:") + 5;
                int endIndex = detectionLine.indexOf("),", startIndex);
                if (endIndex == -1) {
                    endIndex = detectionLine.indexOf(")", startIndex);
                }
                if (startIndex < endIndex) {
                    String lightInfo = detectionLine.substring(startIndex, endIndex);
                    // 去掉中英文括号和"库"字
                    String lightChannel = lightInfo.replaceAll("[()（）]", "").replaceAll("库", "");
                    entity.setLightChannel(lightChannel);
                }
            }

        } catch (Exception e) {
            log.warn("解析检测信息失败", e);
        }
    }

    /**
     * 解析完成度信息
     *
     * @param completionLine 完成度信息行
     * @param entity         实体对象
     */
    private void parseCompletionInfo(String completionLine, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 格式：完成度:100%实验时长：11分22秒

            // 解析完成度
            if (completionLine.contains("完成度:")) {
                int startIndex = completionLine.indexOf("完成度:") + 4;
                int endIndex = completionLine.indexOf("%", startIndex);
                if (startIndex < endIndex) {
                    String completionStr = completionLine.substring(startIndex, endIndex);
                    try {
                        // 去掉%符号，提取数字
                        String state = completionStr.replaceAll("%", "");
                        entity.setState(state);
                    } catch (NumberFormatException e) {
                        log.warn("解析完成度失败: {}", completionStr, e);
                    }
                }
            }

            // 解析实验时长
            if (completionLine.contains("实验时长：")) {
                int startIndex = completionLine.indexOf("实验时长：") + 5;
                String runTime = completionLine.substring(startIndex);
                entity.setRunTime(runTime);
            }

        } catch (Exception e) {
            log.warn("解析完成度信息失败", e);
        }
    }

    /**
     * 解析序列信息
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseSequenceInfo(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 从第2行开始解析序列信息
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    String type = getStringValue(row.getCell(1)); // 类型列
                    String temperature = getStringValue(row.getCell(2)); // 温度列
                    String speed = getStringValue(row.getCell(3)); // 速率列
                    String delay = getStringValue(row.getCell(4)); // 时间列
                    String light = getStringValue(row.getCell(5)); // 测光列

                    // 这里可以根据需要构建序列JSON
                    // 由于序列信息比较复杂，这里只是示例
                    if (type != null && temperature != null) {
                        // 可以在这里构建序列JSON字符串
                        // entity.setSequence(buildSequenceJson(...));
                    }
                }
            }
        } catch (Exception e) {
            log.warn("解析序列信息失败", e);
        }
    }

    /**
     * 解析设备状态表
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseDeviceStateSheet(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 解析软件版本号和模块版本号
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    String key = getStringValue(row.getCell(0));
                    String value = getStringValue(row.getCell(1));

                    if ("软件版本号".equals(key) && value != null) {
                        entity.setSoftwareVersion(value);
                    } else if ("模块版本号".equals(key) && value != null) {
                        entity.setModuleVersion(value);
                    }
                }
            }
        } catch (Exception e) {
            log.warn("解析设备状态表失败", e);
        }
    }

    /**
     * 解析温度数据表
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseTemperatureSheet(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 计算运行时长（根据数据行数）
            int rowCount = sheet.getLastRowNum();
            if (rowCount > 0) {
                // 假设温度数据采样间隔为1秒
                int totalSeconds = rowCount;
                int minutes = totalSeconds / 60;
                int seconds = totalSeconds % 60;
                String runTime = String.format("%d分%d秒", minutes, seconds);
                entity.setRunTime(runTime);
            }
        } catch (Exception e) {
            log.warn("解析温度数据表失败", e);
        }
    }

    /**
     * 解析电压电流数据表
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseVolCurrentSheet(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 这里可以解析电压电流相关的数据
            // 根据实际需要添加解析逻辑
        } catch (Exception e) {
            log.warn("解析电压电流数据表失败", e);
        }
    }


    /**
     * 解析计量数据表
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseMeteringSheet(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 这里可以解析计量相关的数据
            // 根据实际需要添加解析逻辑
            // 解析荧光校准参数数据
            parseFluorescenceCalibrationData(sheet, entity);
        } catch (Exception e) {
            log.warn("解析计量数据表失败", e);
        }
    }

    /**
     * 解析CT数据表
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseCTSheet(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 解析CT值（VIC、Cy5、FAM、ROX）
            Row headerRow = sheet.getRow(0);
            if (headerRow != null) {
                // 查找对应的列索引
                int vicIndex = -1, cy5Index = -1, famIndex = -1, roxIndex = -1;

                for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                    String header = getStringValue(headerRow.getCell(i));
                    if ("Fluore (VIC)".equals(header)) vicIndex = i;
                    else if ("Fluore (Cy5)".equals(header)) cy5Index = i;
                    else if ("Fluore (FAM)".equals(header)) famIndex = i;
                    else if ("Fluore (ROX)".equals(header)) roxIndex = i;
                }

                // 获取第一行数据
                Row dataRow = sheet.getRow(1);
                if (dataRow != null) {
                    if (vicIndex >= 0) {
                        Double vic = getDoubleValue(dataRow.getCell(vicIndex));
                        if (vic != null) entity.setVic(vic);
                    }
                    if (cy5Index >= 0) {
                        Double cy5 = getDoubleValue(dataRow.getCell(cy5Index));
                        if (cy5 != null) entity.setCy5(cy5);
                    }
                    if (famIndex >= 0) {
                        Double fam = getDoubleValue(dataRow.getCell(famIndex));
                        if (fam != null) entity.setFam(fam);
                    }
                    if (roxIndex >= 0) {
                        Double rox = getDoubleValue(dataRow.getCell(roxIndex));
                        if (rox != null) entity.setRox(rox);
                    }
                }
            }
        } catch (Exception e) {
            log.warn("解析CT数据表失败", e);
        }
    }

    /**
     * 解析熔解曲线数据表
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseMeltCurveSheet(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 这里可以解析熔解曲线相关的数据
            // 根据实际需要添加解析逻辑
        } catch (Exception e) {
            log.warn("解析熔解曲线数据表失败", e);
        }
    }

    /**
     * 解析Trim数据表
     *
     * @param sheet  Excel工作表
     * @param entity 实体对象
     */
    private void parseTrimSheet(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            // 这里可以解析Trim相关的数据
            // 根据实际需要添加解析逻辑
        } catch (Exception e) {
            log.warn("解析Trim数据表失败", e);
        }
    }


    /**
     * 熔解结果
     */
    private void parseMeltingResults(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) return;

            int tmVicIdx = -1, tmCy5Idx = -1, tmFamIdx = -1, tmRoxIdx = -1;
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                String header = getStringValue(headerRow.getCell(i));
                if ("Tm_VIC".equals(header)) tmVicIdx = i;
                else if ("Tm_Cy5".equals(header)) tmCy5Idx = i;
                else if ("Tm_FAM".equals(header)) tmFamIdx = i;
                else if ("Tm_ROX".equals(header)) tmRoxIdx = i;
            }
            if (tmVicIdx == -1 && tmCy5Idx == -1 && tmFamIdx == -1 && tmRoxIdx == -1) return;

            java.util.List<Double> tmVicList = new java.util.ArrayList<>();
            java.util.List<Double> tmCy5List = new java.util.ArrayList<>();
            java.util.List<Double> tmFamList = new java.util.ArrayList<>();
            java.util.List<Double> tmRoxList = new java.util.ArrayList<>();

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                if (tmVicIdx != -1) {
                    Double v = getDoubleValue(row.getCell(tmVicIdx));
                    if (v != null) tmVicList.add(v);
                }
                if (tmCy5Idx != -1) {
                    Double v = getDoubleValue(row.getCell(tmCy5Idx));
                    if (v != null) tmCy5List.add(v);
                }
                if (tmFamIdx != -1) {
                    Double v = getDoubleValue(row.getCell(tmFamIdx));
                    if (v != null) tmFamList.add(v);
                }
                if (tmRoxIdx != -1) {
                    Double v = getDoubleValue(row.getCell(tmRoxIdx));
                    if (v != null) tmRoxList.add(v);
                }
            }
        } catch (Exception e) {
            log.warn("解析熔解结果失败", e);
        }
    }


    /**
     * 获取字符串值
     *
     * @param cell Excel单元格
     * @return 字符串值
     */
    private String getStringValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                return String.valueOf(cell.getNumericCellValue());
            default:
                return null;
        }
    }

    private Double getDoubleValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case NUMERIC:
                return cell.getNumericCellValue();
            case STRING:
                try {
                    return Double.parseDouble(cell.getStringCellValue());
                } catch (NumberFormatException e) {
                    return null;
                }
            default:
                return null;
        }
    }

    /**
     * 通用的括号内容提取方法
     * 支持中英文括号
     *
     * @param text 包含括号的文本
     * @return 括号内的内容，如果没有括号则返回null
     */
    private String extractBracketContent(String text) {
        if (text == null || text.isEmpty()) {
            return null;
        }

        // 尝试英文括号
        if (text.contains("(") && text.contains(")")) {
            int start = text.indexOf("(");
            int end = text.indexOf(")", start);
            if (start < end) {
                return text.substring(start + 1, end);
            }
        }

        // 尝试中文括号
        if (text.contains("（") && text.contains("）")) {
            int start = text.indexOf("（");
            int end = text.indexOf("）", start);
            if (start < end) {
                return text.substring(start + 1, end);
            }
        }

        return null;
    }

    /**
     * 通用的括号前内容提取方法
     * 支持中英文括号
     *
     * @param text 包含括号的文本
     * @return 括号前的内容，如果没有括号则返回原文本
     */
    private String extractBeforeBracket(String text) {
        if (text == null || text.isEmpty()) {
            return text;
        }

        // 尝试英文括号
        if (text.contains("(")) {
            return text.substring(0, text.indexOf("("));
        }

        // 尝试中文括号
        if (text.contains("（")) {
            return text.substring(0, text.indexOf("（"));
        }

        return text;
    }

    /**
     * 设置ExperimentalRecordsInstanceEntity的默认值
     *
     * @param entity 实体对象
     */
    private void setDefaultValuesForExperimentalRecords(CalibrationExperimentalRecordsEntity entity) {
        // 设置默认值
        if (entity.getVic() == null) {
            entity.setVic(0.0);
        }
        if (entity.getCy5() == null) {
            entity.setCy5(0.0);
        }
        if (entity.getFam() == null) {
            entity.setFam(0.0);
        }
        if (entity.getRox() == null) {
            entity.setRox(0.0);
        }
        if (entity.getRunTime() == null) {
            entity.setRunTime("0分0秒");
        }
        if (entity.getSoftwareVersion() == null) {
            entity.setSoftwareVersion("V1.0.0");
        }
        if (entity.getModuleVersion() == null) {
            entity.setModuleVersion("V1.0.0");
        }

        // 设置修改状态
        entity.setItemUpStatus(0);
        entity.setSeqUpStatus(0);
        entity.setLightUpStatus(0);

        // 设置其他默认值
        if (entity.getName() == null) {
            entity.setName("实验记录");
        }
        if (entity.getItem() == null) {
            entity.setItem("默认检测项");
        }
        if (entity.getLightChannel() == null) {
            entity.setLightChannel("VIC,Cy5,FAM,ROX");
        }
    }


    /*******************************************************************************************************************/
    /*******************************************************表头校验方法**************************************************/
    /*******************************************************************************************************************/

    /**
     * 校验温度表头
     * 标准表头：Time (s)	RTD_Des (Celsius Degree)	Reagent_Tar(Celsius Degree)	Set (Celsius Degree)	Reagent (Celsius Degree)	RTD_Real (Celsius Degree)	Cooler (Celsius Degree)	Assisted_RTD_A(Celsius Degree)	Assisted_RTD_B(Celsius Degree)	Assisted_RTD_C(Celsius Degree)
     */
    private void validateTemperatureHeaders(Sheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("温度表缺少表头行");
        }

        String[] expectedHeaders = {
            "Time (s)",
            "RTD_Des (Celsius Degree)",
            "Reagent_Tar(Celsius Degree)",
            "Set (Celsius Degree)",
            "Reagent (Celsius Degree)",
            "RTD_Real (Celsius Degree)",
            "Cooler (Celsius Degree)",
            "Assisted_RTD_A(Celsius Degree)",
            "Assisted_RTD_B(Celsius Degree)",
            "Assisted_RTD_C(Celsius Degree)"
        };

        validateHeaders(headerRow, expectedHeaders, "温度");
    }

    /**
     * 校验电压电流表头
     * 标准表头：Time (s)	Main_Voltage (V)	Main_Current (A)	A_Voltage (V)	A_Current (A)	B_Voltage (V)	B_Current (A)	C_Voltage (V)	C_Current (A)
     */
    private void validateVolCurrentHeaders(Sheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("电压电流表缺少表头行");
        }

        String[] expectedHeaders = {
            "Time (s)",
            "Main_Voltage (V)",
            "Main_Current (A)",
            "A_Voltage (V)",
            "A_Current (A)",
            "B_Voltage (V)",
            "B_Current (A)",
            "C_Voltage (V)",
            "C_Current (A)"
        };

        validateHeaders(headerRow, expectedHeaders, "电压电流");
    }

    /**
     * 校验扩增表头
     * 标准表头：Cycle	Fluore (VIC)	Fluore (Cy5)	Fluore (FAM)	Fluore (ROX)
     */
    private void validateAmplificationHeaders(Sheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("扩增表缺少表头行");
        }

        String[] expectedHeaders = {
            "Cycle",
            "Fluore (VIC)",
            "Fluore (Cy5)",
            "Fluore (FAM)",
            "Fluore (ROX)"
        };

        validateHeaders(headerRow, expectedHeaders, "扩增");
    }

    /**
     * 校验荧光（电压）表头
     * 标准表头：Cycle	Fluore(VIC)	Fluore(Cy5)	Fluore(FAM)	Fluore(ROX)	Fluore(VIC) Original(0.1V)	Fluore(Cy5) Original(0.1V)	Fluore(FAM) Original(0.1V)	Fluore(ROX) Original(0.1V)
     */
    private void validateFluorescenceVoltageHeaders(Sheet sheet) {
        if (null == sheet) {
            throw new BusinessException("荧光（电压） sheet页必须存在且有数据");
        }
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("荧光（电压）表缺少表头行");
        }

        String[] expectedHeaders = {
            "Cycle",
            "Fluore(VIC)",
            "Fluore(Cy5)",
            "Fluore(FAM)",
            "Fluore(ROX)",
            "Fluore(VIC) Original(0.1V)",
            "Fluore(Cy5) Original(0.1V)",
            "Fluore(FAM) Original(0.1V)",
            "Fluore(ROX) Original(0.1V)"
        };

        validateHeaders(headerRow, expectedHeaders, "荧光（电压）");
    }

    /**
     * 校验荧光（源数据）表头并解析数据
     * 标准表头：Cycle	Fluore(VIC)	Fluore(Cy5)	Fluore(FAM)	Fluore(ROX)	Fluore(VIC) Original	Fluore(Cy5) Original	Fluore(FAM) Original	Fluore(ROX) Original
     */
    private void validateFluorescenceSourceHeaders(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("荧光（原数据）表缺少表头行");
        }

        String[] expectedHeaders = {
            "Cycle",
            "Fluore(VIC)",
            "Fluore(Cy5)",
            "Fluore(FAM)",
            "Fluore(ROX)",
            "Fluore(VIC) Original",
            "Fluore(Cy5) Original",
            "Fluore(FAM) Original",
            "Fluore(ROX) Original"
        };

        validateHeaders(headerRow, expectedHeaders, "荧光（原数据）");
    }

    /**
     * 解析荧光校准参数数据
     * 读取Fluore(VIC) Original、Fluore(Cy5) Original、Fluore(FAM) Original、Fluore(ROX) Original四列数据
     * 组装成指定格式的JSON结构
     */
    private void parseFluorescenceCalibrationData(Sheet sheet, CalibrationExperimentalRecordsEntity entity) {
        try {
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                return;
            }

            // 查找目标列的索引
            int vicOriginalIndex = -1;
            int cy5OriginalIndex = -1;
            int famOriginalIndex = -1;
            int roxOriginalIndex = -1;

            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                String header = getStringValue(headerRow.getCell(i)).replaceAll("\\s+","");
                if ("Fluore(VIC)Original(0.1V)".equals(header)) {
                    vicOriginalIndex = i;
                } else if ("Fluore(Cy5)Original(0.1V)".equals(header)) {
                    cy5OriginalIndex = i;
                } else if ("Fluore(FAM)Original(0.1V)".equals(header)) {
                    famOriginalIndex = i;
                } else if ("Fluore(ROX)Original(0.1V)".equals(header)) {
                    roxOriginalIndex = i;
                }
            }

            // 如果找不到目标列，直接返回
            if (vicOriginalIndex == -1 && cy5OriginalIndex == -1 && famOriginalIndex == -1 && roxOriginalIndex == -1) {
                log.warn("未找到荧光原始数据列");
                return;
            }

            // 收集各通道的数据
            List<Double> vicData = new ArrayList<>();
            List<Double> cy5Data = new ArrayList<>();
            List<Double> famData = new ArrayList<>();
            List<Double> roxData = new ArrayList<>();

            // 从第2行开始读取数据（第1行是表头）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }

                // 读取VIC数据
                if (vicOriginalIndex != -1) {
                    Double vicValue = getDoubleValue(row.getCell(vicOriginalIndex));
                    if (vicValue != null) {
                        vicData.add(vicValue);
                    }
                }

                // 读取Cy5数据
                if (cy5OriginalIndex != -1) {
                    Double cy5Value = getDoubleValue(row.getCell(cy5OriginalIndex));
                    if (cy5Value != null) {
                        cy5Data.add(cy5Value);
                    }
                }

                // 读取FAM数据
                if (famOriginalIndex != -1) {
                    Double famValue = getDoubleValue(row.getCell(famOriginalIndex));
                    if (famValue != null) {
                        famData.add(famValue);
                    }
                }

                // 读取ROX数据
                if (roxOriginalIndex != -1) {
                    Double roxValue = getDoubleValue(row.getCell(roxOriginalIndex));
                    if (roxValue != null) {
                        roxData.add(roxValue);
                    }
                }
            }

            // 组装JSON数据
            JSONObject calibrationData = new JSONObject();

            if (!vicData.isEmpty()) {
                calibrationData.put("VIC", vicData);
            }
            if (!cy5Data.isEmpty()) {
                calibrationData.put("Cy5", cy5Data);
            }
            if (!famData.isEmpty()) {
                calibrationData.put("FAM", famData);
            }
            if (!roxData.isEmpty()) {
                calibrationData.put("ROX", roxData);
            }

            // 将数据包装成数组格式
            List<JSONObject> calibrationArray = new ArrayList<>();
            calibrationArray.add(calibrationData);

            // 转换为JSON字符串并设置到entity
            String calibrationParamStr = JSON.toJSONString(calibrationArray);
            entity.setFluorescenceCalibrationParam(calibrationParamStr);

            log.info("荧光校准参数解析完成，VIC: {}条，Cy5: {}条，FAM: {}条，ROX: {}条",
                    vicData.size(), cy5Data.size(), famData.size(), roxData.size());

        } catch (Exception e) {
            log.warn("解析荧光校准参数数据失败", e);
        }
    }

    /**
     * 校验CT表头
     * 标准表头：Fluore (VIC)	Fluore (Cy5)	Fluore (FAM)	Fluore (ROX)
     */
    private void validateCTHeaders(Sheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("CT表缺少表头行");
        }

        String[] expectedHeaders = {
            "Fluore (VIC)",
            "Fluore (Cy5)",
            "Fluore (FAM)",
            "Fluore (ROX)"
        };

        validateHeaders(headerRow, expectedHeaders, "CT");
    }

    /**
     * 校验熔解（原数据）表头
     * 标准表头：VIC_T (Celsius Degree)	Fluore (VIC)	Cy5_T (Celsius Degree)	Fluore (Cy5)	FAM_T (Celsius Degree)	Fluore (FAM)	ROX_T (Celsius Degree)	Fluore (ROX)
     */
    private void validateMeltCurveHeaders(Sheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("熔解（原数据）表缺少表头行");
        }

        String[] expectedHeaders = {
            "VIC_T (Celsius Degree)",
            "Fluore (VIC)",
            "Cy5_T (Celsius Degree)",
            "Fluore (Cy5)",
            "FAM_T (Celsius Degree)",
            "Fluore (FAM)",
            "ROX_T (Celsius Degree)",
            "Fluore (ROX)"
        };

        validateHeaders(headerRow, expectedHeaders, "熔解（原数据）");
    }

    /**
     * 校验熔解（后处理）表头
     * 标准表头：T_VIC	Diff (VIC)	T_Cy5	Diff (Cy5)	T_FAM	Diff (FAM)	T_ROX	Diff (ROX)
     */
    private void validateMeltPostProcessHeaders(Sheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("熔解（后处理）表缺少表头行");
        }

        String[] expectedHeaders = {
            "T_VIC",
            "Diff (VIC)",
            "T_Cy5",
            "Diff (Cy5)",
            "T_FAM",
            "Diff (FAM)",
            "T_ROX",
            "Diff (ROX)"
        };

        validateHeaders(headerRow, expectedHeaders, "熔解（后处理）");
    }

    /**
     * 校验熔解结果表头
     * 标准表头：Tm_VIC	Rm_VIC	Tm_Cy5	Rm_Cy5	Tm_FAM	Rm_FAM	Tm_ROX	Rm_ROX
     */
    private void validateMeltingResultsHeaders(Sheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new IllegalArgumentException("熔解结果表缺少表头行");
        }

        String[] expectedHeaders = {
            "Tm_VIC",
            "Rm_VIC",
            "Tm_Cy5",
            "Rm_Cy5",
            "Tm_FAM",
            "Rm_FAM",
            "Tm_ROX",
            "Rm_ROX"
        };

        validateHeaders(headerRow, expectedHeaders, "熔解结果");
    }

    /**
     * 通用表头校验方法
     *
     * @param headerRow       表头行
     * @param expectedHeaders 期望的表头数组
     * @param sheetName       表名（用于错误提示）
     */
    private void validateHeaders(Row headerRow, String[] expectedHeaders, String sheetName) {
        int actualColumnCount = headerRow.getLastCellNum();
        int expectedColumnCount = expectedHeaders.length;

        if (actualColumnCount < expectedColumnCount) {
            throw new IllegalArgumentException(String.format("%s表头列数不足，期望%d列，实际%d列", sheetName, expectedColumnCount, actualColumnCount));
        }

        for (int i = 0; i < expectedColumnCount; i++) {
            String expectedHeader = expectedHeaders[i];
            String actualHeader = getStringValue(headerRow.getCell(i));

            if (actualHeader == null || !actualHeader.replaceAll("\\s+", "").equals(expectedHeader.replaceAll("\\s+", ""))) {
                throw new IllegalArgumentException(String.format("%s表第%d列表头不匹配，期望：%s，实际：%s",
                                                                 sheetName, i + 1, expectedHeader, actualHeader));
            }
        }
    }

    /**
     * 校验Excel文件中的sheet页
     * 确保Excel文件包含所有必需的sheet页，且数量和名称都不能有偏差
     *
     * @param workbook Excel工作簿
     * @throws IllegalArgumentException 当sheet页数量或名称不符合要求时抛出异常
     */
    private void validateExcelSheets(Workbook workbook) {
        // 定义必需的sheet页名称
        Set<String> requiredSheetNames = new HashSet<>(Arrays.asList(
            "实验信息",
            "状态信息",
            "温度",
            "电压电流",
            "扩增",
            "荧光（电压）",
            "荧光（原数据）",
            "CT",
            "熔解（原数据）",
            "熔解（后处理）",
            "熔解结果"
        ));

        // 获取实际的sheet页名称
        Set<String> actualSheetNames = new HashSet<>();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            String sheetName = workbook.getSheetName(i);
            if (sheetName != null && !sheetName.trim().isEmpty()) {
                actualSheetNames.add(sheetName.trim());
            }
        }

        // 检查sheet页数量
        if (actualSheetNames.size() != requiredSheetNames.size()) {
            throw new IllegalArgumentException(String.format(
                "Excel文件sheet页数量不正确，期望%d个，实际%d个。期望的sheet页：%s，实际的sheet页：%s",
                requiredSheetNames.size(),
                actualSheetNames.size(),
                String.join(", ", requiredSheetNames),
                String.join(", ", actualSheetNames)
            ));
        }

        // 检查sheet页名称是否完全匹配
        if (!actualSheetNames.equals(requiredSheetNames)) {
            // 找出缺失的sheet页
            Set<String> missingSheets = new HashSet<>(requiredSheetNames);
            missingSheets.removeAll(actualSheetNames);

            // 找出多余的sheet页
            Set<String> extraSheets = new HashSet<>(actualSheetNames);
            extraSheets.removeAll(requiredSheetNames);

            StringBuilder errorMessage = new StringBuilder("Excel文件sheet页名称不符合要求：");

            if (!missingSheets.isEmpty()) {
                errorMessage.append(String.format("缺失的sheet页：%s；", String.join(", ", missingSheets)));
            }

            if (!extraSheets.isEmpty()) {
                errorMessage.append(String.format("多余的sheet页：%s；", String.join(", ", extraSheets)));
            }

            errorMessage.append(String.format("期望的sheet页：%s", String.join(", ", requiredSheetNames)));

            throw new IllegalArgumentException(errorMessage.toString());
        }

        log.info("Excel文件sheet页校验通过，包含{}个sheet页：{}",
                 actualSheetNames.size(), String.join(", ", actualSheetNames));
    }

}
